import xlrd, xlwt
from xlutils.copy import copy
from xlrd import xldate_as_tuple
from datetime import datetime


def SetBoeder():
    """为样式创建边框"""
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    return borders


def Background():
    """背景颜色"""
    pattern = xlwt.Pattern()  # 设置背景颜色
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置背景颜色的模式
    pattern.pattern_fore_colour = 21  # 背景颜色
    return pattern


def ReadExcel(file_name):
    row_index = 5
    time_list = []  # 获取时间
    data = xlrd.open_workbook(file_name)  # 读取Excel
    table = data.sheets()[0]  # 获取第一个表
    nrows = table.nrows  # 获取该sheet中的有效行数
    title_date = table.row_values(1)  # 返回由该行数据，类型列表
    dict_write_data = {}
    # 获取每行数据组织json格式
    for index in range(5, nrows):
        col_data = table.cell_value(index, 19)  # 第一个数据是行，第二个是列
        # python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype:
        # ctype： 0  empty, 1 string, 2 number, 3 date, 4 boolean, 5 error
        col = table.cell(index, 19).ctype
        if col == 3:
            # 转成datetime对象
            date = datetime(*xldate_as_tuple(col_data, 0))
            time_list.append(date)
        for_rows = table.row_values(index)  # 循环获取整行数据
        for_rows[0] = int(for_rows[0])
        dict_write_data[for_rows[0]] = for_rows
    new_data = copy(data)  # 将xlrd对象拷贝转化为xlwt对象,xlutils的copy是xlrd和xlwt桥梁
    ws = new_data.get_sheet(0)  # 获取第一个工作表信息
    # 重新写入数据，为数据增加边框
    for key_index, value in enumerate(dict_write_data.values()):
        col_index = 0
        for i in value:
            style1 = xlwt.XFStyle()
            style1.borders = SetBoeder()
            ws.write(row_index, col_index, i, style1)
            col_index += 1
        row_index += 1
        # 处理时间样式
        dateFormat = xlwt.XFStyle()
        dateFormat.num_format_str = 'yyyy-mm-dd hh:mm:ss'
        dateFormat.borders = SetBoeder()
        ws.col(19).width = 4400
        ws.write(key_index + 5, 19, time_list[key_index], dateFormat)
    # 为前五行数据增加边框和 表头名添加颜色
    cutting_data = {}
    for row in range(5):
        cutting = table.row_values(row)
        cutting_data[row] = cutting
    for index, key in enumerate(cutting_data.values()):
        title_col_index = 0
        for value in key:
            title_style = xlwt.XFStyle()
            title_style.borders = SetBoeder()
            if index == 1:
                title_style.pattern = Background()
            ws.write(index, title_col_index, value, title_style)
            title_col_index += 1
    new_data.save(r'./0011(1).xls')
    return True


if __name__ == '__main__':
    ReadExcel(r'./0011(1).xls')
